import json
import requests
import pymysql

#协同平台-船舶安全监督管理系统-船舶现场监督检查
BASE_URL = 'http://cmp.msa.gov.cn/supervision/supervision/handle.action?act=spvisionQuery&is_wxd=0&show_style=1&siteSpvisionQO.inspect_date_start=2024-10-01&siteSpvisionQO.inspect_date_end=2024-12-31&siteSpvisionQO.org_code=140000&siteSpvisionQO.org_limit=1'
POST_URL = 'http://cmp.msa.gov.cn/supervision/supervision/handle.action?act=getDetailList&siteSpvisionPO.inspect_no='
headers = {
    'Accept': 'application/json, text/javascript, */*; q=0.01',
    # Content-Type设置为application/json，接口才能返回json数据
    'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8'
}
cookies = {
    # OAMAuthnCookie_cmp.msa.gov.cn:80 参数用于登录认证
    'OAMAuthnCookie_cmp.msa.gov.cn:80': '8qMY57KpQb67cMOjEE0WBDaRdVNUNDL5Alwg1zifGo4wQgNynSd2mq0ZDPMiFOgCSMgw%2F71eFC5C4H2j%2Feb9lbhYvVhkIRBQYwAxOdDTIzGy%2FRLsY8FHKsrJASMYh19HKgaxxyhRGIdOFvG5h4vKxzjfpRXHsAR6QxDKDc5oRXqgT9ZHIcf8gJKOCdoWzEsX9nD9De4QyIrJXDdVza55S0xEauc9bNbcL79EmRiHO%2FLma9%2BqWWJL8KFAzHrIbOLCX6cR9PZ7BZVSDmPKhr6HpmjicE%2F%2B222DkNl3hIIskiwWa4yQqFFd8uTCVr99kOHhnow224S4jxLQ2WSusQjK4GuXlcs1by6U7nK1Y3bo2HzR4pMhMsZ%2BndoV0B1NDOP28sdG%2Ff6mcBaSCVHqp9hkky65XEQtN1MGAnUIr1Fyov2iL15ZPOKeUFFL9s3hMsWDGo5QoAODZsfqpcnkHBi1WtCZSRxbNXoi7JIWBVp8Nno%3D',
    # OAMAuthnHintCookie和JSESSIONID 参数用于声明返回接口数据，不配置的会一直返回HTML
    'OAMAuthnHintCookie': '1',
    'JSESSIONID': '0-NV48uPInGghmXS2Z8i8xKbBMVkyvaM51guxzL_ePnYlI8hp7O5!1701920017'
}

session = requests.Session()
# 数据库连接配置
db_config = {
    'host': '191.254.8.220',
    'user': 'root',
    'password': 'Szmsa@520',
    'db': 'spiders',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}
# 表名和字段名
table_name = 'xt_safety_ship_local_check_36_37'
table_auditor = 'xt_safety_ship_local_check_question_36_37'
# 连接数据库
connection = pymysql.connect(**db_config)


def insertData(ship_region_type):
    try:
        print(ship_region_type)
        response_lo = session.post(BASE_URL+'&shipDataQO.ship_region_type='+ship_region_type, headers=headers, cookies=cookies)
        print(response_lo.text)
        datas = json.loads(response_lo.text)
        if response_lo.status_code == 200:
            # 开始事务
            with connection.cursor() as cursor:
                for record in datas:
                    # 获取需要插入的字段名称
                    fields = ', '.join(record.keys())
                    # 拼装需要插入字段对应的%号个数
                    values = ", ".join(['%s'] * len(record))
                    inspect_no = record['inspect_no']

                    # value值是json的字段无法直接存入库，要转成string
                    json_inspect_date = record['inspect_date']
                    json_create_time = record['create_time']
                    json_operate_time = record['operate_time']

                    # json对象转成string后重新赋值回原来的字段
                    str_inspect_date = json.dumps(json_inspect_date)
                    str_create_time = json.dumps(json_create_time)
                    str_operate_time = json.dumps(json_operate_time)

                    record['inspect_date'] = str_inspect_date
                    record['create_time'] = str_create_time
                    record['operate_time'] = str_operate_time
                    # 构建插入语句
                    insert_stmt = (
                        "INSERT INTO `{table}` ({fields},ship_region_type) "
                        "VALUES ({values},{ship_region_type})"
                    ).format(
                        table=table_name,
                        fields=fields,
                        values=values,
                        ship_region_type=ship_region_type
                    )
                    # 执行sql语句插入数据，record.values()用于获取字段中的值
                    cursor.execute(insert_stmt, tuple(record.values()))

                    response_auditor = session.get(POST_URL + inspect_no, headers=headers, cookies=cookies)
                    print(response_auditor.text)
                    auditorData = json.loads(response_auditor.text)
                    if response_auditor.status_code == 200:
                        for auditor in auditorData:
                            # 获取需要插入的字段名称
                            auditor_fields = ', '.join(auditor.keys())
                            # 拼装需要插入字段对应的%号个数
                            auditor_values = ", ".join(['%s'] * len(auditor))
                            # value值是json的字段无法直接存入库，要转成string
                            json_create_time = auditor['create_time']
                            json_operate_time = auditor['operate_time']

                            # json对象转成string后重新赋值回原来的字段
                            str_create_time = json.dumps(json_create_time)
                            str_operate_time = json.dumps(json_operate_time)

                            auditor['create_time'] = str_create_time
                            auditor['operate_time'] = str_operate_time

                            insert_auditor = (
                                "INSERT INTO `{table}` ({fields}) "
                                "VALUES ({values})"
                            ).format(
                                table=table_auditor,
                                fields=auditor_fields,
                                values=auditor_values
                            )
                            cursor.execute(insert_auditor, tuple(auditor.values()))
            connection.commit()
            print('插入完成!')
    except Exception as e:
        print(f'insert fail::{e}')
        connection.rollback()
        pass


try:
    # 现场监督检查-海船
    insertData('1')
    # 现场监督检查-内河船
    insertData('0')
finally:
    # 关闭数据库连接
    connection.close()